<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/* Start of file giroes.php */
/* Location: ./cashflow/models/giroes.php */
class Transfers extends CI_Model {
    
	function __construct(){
        parent::__construct(); /** Call the Model constructor */
    }
    
    function getAllTransfer($day,$day2,$acc){
        $this->db->select(" DISTINCT NomorSurat,DATE_FORMAT(t.Tanggal,'%d-%m-%y') as Tanggal",FALSE);
        $this->db->where('t.Tanggal >=',$day);
        $this->db->where('t.Tanggal <=',$day2);
        $this->db->where('t.NoRekening',$acc);
        $this->db->order_by("t.NomorSurat",'ASC');
        $Q=$this->db->get('transfer t');
        $data = $Q->result_array();
        $Q->free_result();
		return $data;      
    }
    
    function getTransferPayment($no){
        $this->db->select('Uraian,Penerima,DeptId,NomorSurat,Payment');
        $this->db->where('NomorSurat',$no);
        $this->db->order_by("ABS(pdo_id)",'ASC');
		$Q=$this->db->get('pdo_transfer');
        $data = $Q->result_array();
        $Q->free_result();
		return $data;   
	}
    
    function getCountTransfer($no){
        $this->db->select('sum(Payment) as total_payment,count(pdo_id) as jml_cek');
        $this->db->where('NomorSurat',$no);    
        $Q=$this->db->get('pdo_transfer');
        return $Q->row_array();
    }
    
    function getExcel($day,$day2,$acc){
        $transfers   =   $this->getAllTransfer($day,$day2,$acc); /** call a function getAllGiroes Table giro **/
        $objPHPExcel = new PHPExcel(); /** create a new object excel **/
        /** excel properties */
        $objPHPExcel->getProperties()->setTitle("Rekap Transfer")
					->setDescription("description");
					 $objPHPExcel->setActiveSheetIndex(0);
                     $objPHPExcel->getActiveSheet()->setTitle('Rekap Transfer');
        
        /** excel Page Setup */
        $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(Worksheet_PageSetup::ORIENTATION_PORTRAIT);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(Worksheet_PageSetup::PAPERSIZE_A4);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0.70);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.25);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.37);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0.70);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setScale(70);
        
        /** create a border **/
        $border = array('borders' => array( 'allborders' => array('style'=>Style_Border::BORDER_THIN )));
        /** create a fill coloumn **/
        $fill        = array('type'       => Style_Fill::FILL_SOLID,'rotation'=>0,'startcolor'=>array('rgb'=>'CCCCCC'),'endcolor'=>array('argb' => 'CCCCCC'));
        
        /** first row title page **/
        $row = 1;
        $col = 0;
        /** Account  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'Rekap Transfer No Rekening.'.$acc);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+7,$row);
        
        /** counter row next title page **/
        $row = $row + 1;
        $col = 0;
        /** Company Name  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'PT. Bayu Buana Gemilang');
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+7,$row);
        
        /** counter row next title page **/
        $row = $row + 1;
        $col = 0;
        /** Periode  **/	
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,'Periode '.indonesian_date($day).' s/d '.indonesian_date($day2));
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setVertical(Style_Alignment::VERTICAL_TOP);
		$objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        
        /** merge cell from col 0 to col 7 **/
        $objPHPExcel->getActiveSheet()->mergeCellsByColumnAndRow($col,$row,$col+7,$row);
        
         /** row & col Column Page */ 
        $row = $row + 2; /** row +1*/
        $col = 0; /** coloumn 1*/ 
        
        /** coloumn No Col 00 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,lang('no')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col)->setWidth(5); 
        
        /** coloumn Date Col 01 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,lang('date')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+1)->setWidth(10); 
        
        /** coloumn Information Col 02 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,lang('information')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+2)->setWidth(50); 
        
        /** coloumn Supplier Col 03 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,lang('supplier')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+3)->setWidth(25); 
        
        /** coloumn Dept Col 04 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,lang('dept')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+4)->setWidth(7); 
        
        /** coloumn Detail Col 05 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,lang('detail')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+5)->setWidth(15);
        
        /** coloumn Total Col 06 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,lang('total')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+6)->setWidth(15);  
        
        /** coloumn No Surat Col 07 */
		$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,lang('no')); 
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->getAlignment()->setHorizontal(Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->applyFromArray($border);
        $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->getFill()->applyFromArray($fill);
        $objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($col+7)->setWidth(10);  
        
        /** row & col Column Content */ 
        $row = $row + 1; /** row +1*/
        $col = 0; /** coloumn 1*/ 
        $i=1;  /** no **/
        
        foreach($transfers as $rec):
            $value = $this->getTransferPayment($rec['NomorSurat']); /** retrieve get Giroes function **/
            $j = 1; /** counter j **/
            foreach($value as $val):
                $sum = $this->getCountTransfer($rec['NomorSurat']); /** get count & total payment from giro **/
                if($j==$sum['jml_cek']): /** if $j == $row to Jumlah cek **/
                    $pay     =   $sum['total_payment'];
                    $trf     =   $rec['NomorSurat'];
                else:
   					$pay     =   ""; 
   					$trf     =   "";
                endif;
                
                /** coloumn No **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,$i);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col,$row)->applyFromArray($border);
                
                /** coloumn Date **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+1,$row,$rec['Tanggal']);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+1,$row)->applyFromArray($border);
                
                /** coloumn Information **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+2,$row,$val['Uraian']);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->getAlignment()->setWrapText(true);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+2,$row)->applyFromArray($border);
                
                /** coloumn Supplier **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+3,$row,$val['Penerima']);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+3,$row)->applyFromArray($border);
                
                /** coloumn Department **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+4,$row,$val['DeptId']);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+4,$row)->applyFromArray($border);
                
                /** coloumn Payment **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+5,$row,decimal($val['Payment'],2));
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+5,$row)->applyFromArray($border);
                
                /** coloumn Total Payment **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+6,$row,decimal($pay,2));
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+6,$row)->applyFromArray($border);
                
                /** coloumn Giro **/ 
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col+7,$row,$trf);
                $objPHPExcel->getActiveSheet()->getStyleByColumnAndRow($col+7,$row)->applyFromArray($border);
                    
                $j++; /** counter to row variabel j **/ 
                $i++; /** counter to row variabel i **/ 
                $row++; /** counter to row excel **/    
            endforeach;
        endforeach;
        
        /** Save it as an excel 2007 file */
        $objWriter = IOFactory::createWriter($objPHPExcel, "Excel2007");
        $file = "Rekap-Transfer.xls";
        $objWriter->save($file);
        return $file;
    }
    
    
}    